﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IBM.Data.DB2;
using System.Collections.Specialized;
using System.Web.Mvc;
using CourseManager.Domain.Entities;

namespace CourseManager.Domain.Repositories
{
    public class InstructorRepository
    {
        DatabaseTransac DatabaseTans;

        public List<Instructor> GetInstructor()
        {
            Instructor Instructor = new Instructor();
            string Sql = "select Id,Concat(Name,LastName) as Name  "
                        + "from {0}.Instructor";
         
            Sql = string.Format(Sql, Properties.Settings.Default.Schema);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Instructor
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Name = dr[1].ToString()
                          }).OrderBy(i => i.Name);

            return Result.ToList();
        
        }

        public List<Instructor> GetInstructorByName(string pName)
        {
            Instructor Instructor = new Instructor();
            string Sql = "select Id,Name,LastName,Telephone,Cellphone,Address,Email "
                        + "from {0}.Instructor where Name like '%{1}%';";

            Sql = string.Format(Sql, Properties.Settings.Default.Schema,pName);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Instructor
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Name = dr[1].ToString(),
                              LastName = dr[2].ToString(),
                              Telephone = dr[3].ToString(),
                              Cellphone = dr[4].ToString(),
                              Address = dr[5].ToString(),
                              Email = dr[6].ToString()
                          }).OrderBy(i => i.Name);

            return Result.ToList();

        }

        public IEnumerable<Instructor> GetInstructorList()
        {
            Instructor Instructor = new Instructor();
            string Sql = "select Id,Name,LastName,Telephone,Cellphone,Address,Email "
                        + "from {0}.Instructor";

            Sql = string.Format(Sql, Properties.Settings.Default.Schema);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Instructor
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Name = dr[1].ToString()
                          }).OrderBy(i => i.Name);

            return Result.AsEnumerable();

        }

        public void AddInstructor(Instructor pInstructor)
        {
            NameValueCollection ValuesToInsert = new NameValueCollection();

            ValuesToInsert.Add("Name", "'" + pInstructor.Name.ToString() + "'");
            ValuesToInsert.Add("LastName", "'" + pInstructor.LastName + "'");
            ValuesToInsert.Add("Telephone", "'" + pInstructor.Telephone.ToString() + "'");
            ValuesToInsert.Add("Cellphone", "'" + pInstructor.Cellphone.ToString() + "'");
            ValuesToInsert.Add("Email", "'" + pInstructor.Email.ToString() + "'");
            ValuesToInsert.Add("Address", "'" + pInstructor.Address.ToString() + "'");

            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeInsert(Properties.Settings.Default.Schema + ".Instructor", ValuesToInsert);
        
        }

        public void UpdateInstructor(Instructor pInstructor)
        {
            NameValueCollection ValuesToInsert = new NameValueCollection();

            ValuesToInsert.Add("Name", "'" + pInstructor.Name.ToString() + "'");
            ValuesToInsert.Add("LastName", "'" + pInstructor.LastName + "'");
            ValuesToInsert.Add("Telephone", "'" + pInstructor.Telephone.ToString() + "'");
            ValuesToInsert.Add("Cellphone", "'" + pInstructor.Cellphone.ToString() + "'");
            ValuesToInsert.Add("Email", "'" + pInstructor.Email.ToString() + "'");
            ValuesToInsert.Add("Address", "'" + pInstructor.Address.ToString() + "'");

            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeUpdate(Properties.Settings.Default.Schema + ".Instructor", ValuesToInsert, new List<string> { "Id= " + pInstructor.Id });

        }

        public void DeleteInstructor(int pId)
        {
            try
            {
                DatabaseTans = new DatabaseTransac();
                DatabaseTans.executeDelete(Properties.Settings.Default.Schema + ".Instructor", new List<string> { "Id = " + pId });

            }
            catch (Exception)
            {

                throw;
            }

        }

    }
}
